import time
import random
import pymysql
import asyncio

db_2m = {
    'host': 'rm-2zeqn1m3b6ik438mz.mysql.rds.aliyuncs.com',  # 主机
    'user': 'tanfeng',  # 用户名
    'password': 'Tf@654321',  # 密码
    'port': 3306,  # 端口 3306
    'database': 'ppg'  # 数据库名
}

db_wm = {
    'host': 'rm-2zev27ct5v8p9ssnyvo.mysql.rds.aliyuncs.com',  # 主机
    'user': 'tanfeng',  # 用户名
    'password': 'Tf@654321',  # 密码
    'port': 3306,  # 端口 3306
    'database': 'nft'  # 数据库名
}


async def generate_unique_bigint():
    timestamp = int(time.time() * 1000)
    random_number = random.randint(0, 999999)
    unique_bigint = int(str(timestamp) + str(random_number))
    return unique_bigint

async def main():
    table_blade_dict = "blade_dict"
    table_blade_dict_parent_id = 169081328805663566
    table_blade_dict_code = "spuInventory"
    # 连接2m库
    connection_2m = pymysql.connect(**db_2m)
    # 连接wm库
    connection_wm = pymysql.connect(**db_wm)
    # 创建游标对象
    cursor_2m = connection_2m.cursor()
    cursor_wm = connection_wm.cursor()
    # 选择要导入的表名
    try:
        cursor_2m.execute(f"select `name`,count(*) from t_sku where activity_no in ('ACT2023040901','ACT20220530000003','ACT2023082901') and `status` in ('02','04') and spu_type='01'  GROUP BY `name`;")
        rows = cursor_2m.fetchall()
        for row in rows:
            spuName = row[0]
            spuCount = row[1]
            sql_select = f"select * from {table_blade_dict} where parent_id=%s and `code`= %s and dict_value = %s limit 1;"
            data = (table_blade_dict_parent_id, table_blade_dict_code, spuName)
            cursor_wm.execute(sql_select, data)
            result = cursor_wm.fetchone()
            if result is not None:
                updateSql = f"update {table_blade_dict} set dict_key=%s+sort where parent_id=%s and `code`=%s and dict_value = %s"
                updateData = (spuCount, table_blade_dict_parent_id, table_blade_dict_code, spuName)
                cursor_wm.execute(updateSql, updateData)
                connection_wm.commit()
                print(f"{spuName}->已更新流通")
            else:
                sql_insert = f"INSERT INTO {table_blade_dict} (id, parent_id, code, dict_key, dict_value, sort) VALUES (%s, %s, %s, %s, %s, %s)"
                unique_id = await generate_unique_bigint()
                data = (unique_id, table_blade_dict_parent_id, table_blade_dict_code, spuCount, spuName, 0)
                cursor_wm.execute(sql_insert, data)
                connection_wm.commit()
                print(f"{spuName}->新增流通")
        print("流通更新成功！")
    except (pymysql.Error) as error:
        print("导入数据表时发生错误:", error)

    # 关闭游标和连接
    cursor_wm.close()
    cursor_wm.close()


if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())